--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';

--CREATE TABLE se_cli_cliassurhis AS (SELECT * FROM &erp.cli_cliassurhis WHERE 1=2);







DELETE FROM se_cli_cliassurhis;

 INSERT INTO se_cli_cliassurhis
 (SELECT id client_cod,
         '1' assregr_cod,
         '1' ass_typ,
         To_Date('31/12/1967','dd/mm/yyyy')+HDASSINT ass_dat,
         0 enreg_id,
         (SELECT HASSINT FROM UV_CCLIENTS_HASSINT a WHERE a.id=c.id AND a.RANGMULTI3=c.RANGMULTI3) ass_mt

    FROM UV_CCLIENTS_HDASSINT c) ;

 INSERT INTO se_cli_cliassurhis
 (SELECT id client_cod,
         '1' assregr_cod,
         '2' ass_typ,
         To_Date('31/12/1967','dd/mm/yyyy')+HDASSEXP ass_dat,
         0 enreg_id,
         (SELECT HASSEXP FROM UV_CCLIENTS_HASSEXP a WHERE a.id=c.id AND a.RANGMULTI3=c.RANGMULTI3) ass_mt

    FROM UV_CCLIENTS_HDASSEXP c) ;

 INSERT INTO se_cli_cliassurhis
 (SELECT id client_cod,
         '1' assregr_cod,
         '3' ass_typ,
         To_Date('31/12/1967','dd/mm/yyyy')+HDASSFRA ass_dat,
         0 enreg_id,
         (SELECT HASSFRA FROM UV_CCLIENTS_HASSFRA a WHERE a.id=c.id AND a.RANGMULTI3=c.RANGMULTI3) ass_mt

    FROM UV_CCLIENTS_HDASSFRA c) ;

 --UPDATE se_cli_cliassurhis SET ass_mt=NULL WHERE ass_mt=0 OR ass_mt IS NULL;



 UPDATE se_cli_cliassurhis tf SET enreg_id=(select enreg_id FROM &erp.cli_cliassurhis f WHERE f.client_cod=tf.client_cod and f.assregr_cod=tf.assregr_cod and f.ass_typ=tf.ass_typ and f.ass_dat=tf.ass_dat) ;
 UPDATE se_cli_cliassurhis tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;



 CALL uvse_synchroverserp('client_cod@assregr_cod@ass_typ@ass_dat','CLI_CLIASSURHIS','O') ;

 commit;


SELECT &outils.stragg(source) source,client_cod, assregr_cod, ass_typ, ass_dat, enreg_id, ass_mt
FROM (
  SELECT 'uv' source ,g.* FROM se_cli_cliassurhis g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.cli_cliassurhis g
  )
GROUP BY client_cod, assregr_cod, ass_typ, ass_dat, enreg_id, ass_mt
HAVING Count(1)!=2
ORDER BY client_cod,assregr_cod;





